Imports System.Collections
Imports System.Collections.Specialized
Imports System.Security.Cryptography
Imports System.Text
Imports System.Configuration
Imports nsDataProvider = KDESolutions.Providers.DataProvider

Public Class DBForumProvider
    Inherits ForumProvider

    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString, _TablePrefix As String

    Public Const cTrueValue As String = "Y"
    Public Const cFalsevalue As String = "N"

    Public Property TablePrefix() As String
        Get
            Return _TablePrefix
        End Get
        Set(ByVal value As String)
            _TablePrefix = value
        End Set
    End Property

    Public Property MyDBType() As String
        Get
            Return _DBType
        End Get
        Set(ByVal value As String)
            _DBType = value
        End Set
    End Property

    Public Overrides ReadOnly Property Name() As String
        Get
            Return "KDESolutions.Providers.Forums.DBForumProvider"
        End Get
    End Property

    Public Overrides ReadOnly Property Description() As String
        Get
            Return "Provider for storing posted messages"
        End Get
    End Property

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If

        If ((name Is Nothing) OrElse (name.Length = 0)) Then
            name = "ForumProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "KDESolutions Forums provider")
        End If

        If String.IsNullOrEmpty(config("tablePrefix")) Then
            config.Remove("tablePrefix")
            config.Add("tablePrefix", "Forums")
            _TablePrefix = "forums"
        Else
            _TablePrefix = config("tablePrefix")
        End If

        'Start of config file settings for database connection ******
        If String.IsNullOrEmpty("DataProviderGroup") Then
            Throw New ArgumentNullException("You must provide a DataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("DataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
                Me.MyDBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If
    End Sub

    Public Overrides Function ApprovePost(ByVal postID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & TablePrefix & "_Posts SET Approved = ? WHERE PostID = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@Approved", cTrueValue, DbType.String)
        DAP.AddParameter("@PostId", postID, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        DAP.ExecuteNonQuery(sSQL)

        Dim parentPostId As String = String.Empty
        Dim AddedDate As DateTime
        Dim AddedBy As String = String.Empty
        sSQL = "SELECT ParentPostID, AddedDate, AddedBy FROM " & _TablePrefix & "_Posts WHERE PostID = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@PostId", postID, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        For Each reader As Common.DbDataRecord In result
            parentPostId = reader.Item("ParentPostId")
            AddedDate = reader.Item("AddedDate")
            AddedBy = reader.Item("AddedBy")
        Next
        If String.IsNullOrEmpty(parentPostId) = False Then
            sSQL = "UPDATE " & _TablePrefix & "_Posts SET ReplyCount = ReplyCount + 1, LastPostDate = ?, LastPostBy = ?" & _
            "WHERE PostID = @ParentPostID AND LogicalDeleteInd = ?"
            DAP.AddParameter("@LastPostDate", AddedDate, DbType.DateTime)
            DAP.AddParameter("@LastPostBy", AddedBy, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
            Return True
        Else
            Return False
        End If
    End Function

    Public Overrides Function CloseThread(ByVal threadPostID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Posts SET Closed = ? " & _
        "WHERE PostID = ?"
        DAP.AddParameter("@Closed", cTrueValue, DbType.String)
        DAP.AddParameter("@PostId", threadPostID, DbType.String)
        If DAP.ExecuteNonQuery(sSQL) >= 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Overrides Function DeleteForum(ByVal forumID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Forums SET LogicalDeleteInd = ? WHERE ForumID = ?"
        DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
        DAP.AddParameter("@ForumId", forumID, DbType.String)
        If DAP.ExecuteNonQuery(sSQL) >= 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Overrides Function DeletePost(ByVal postID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim ParentPostId As String = String.Empty
        Dim sSQL As String = "SELECT ParentPostID FROM " & _TablePrefix & "_Posts WHERE PostID = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@PostId", postID, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        ParentPostId = CType(DAP.ExecuteScalar(sSQL), String)
        Dim resultParentDelete As Integer = 0

        If String.IsNullOrEmpty(ParentPostId) Then
            sSQL = "UPDATE " & _TablePrefix & "_Posts SET LogicalDeleteInd = ? WHERE PostId = ? OR ParentPostId = ?"
            DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
            DAP.AddParameter("@PostId", postID, DbType.String)
            DAP.AddParameter("@ParentPostId", postID, DbType.String)
            resultParentDelete = DAP.ExecuteNonQuery(sSQL)
        Else
            sSQL = "UPDATE " & _TablePrefix & "_Posts SET LogicalDeleteInd = ? WHERE PostId = ?"
            DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
            DAP.AddParameter("@PostId", postID, DbType.String)
            DAP.ExecuteNonQuery(sSQL)

            sSQL = "UPDATE " & _TablePrefix & "_Posts SET ReplyCount = (ReplyCount - 1) WHERE PostId = ? AND LogicalDeleteInd = ?"
            DAP.AddParameter("@PostId", ParentPostId, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        End If
    End Function

    Public Overrides Function GetForumByID(ByVal forumID As String) As DataTypes.ForumDetails
        Dim returnDetails As New DataTypes.ForumDetails
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT ForumID, AddedDate, AddedBy, Title, Moderated, Importance, Description, ImageUrl " & _
        "FROM " & _TablePrefix & "_Forums WHERE ForumID = ?"
        DAP.AddParameter("@ForumID", forumID, DbType.String)
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        For Each myReader As Common.DbDataRecord In result
            returnDetails.ID = myReader.Item("ForumId")
            returnDetails.AddedDate = myReader.Item("AddedDate")
            returnDetails.AddedBy = myReader.Item("AddedBy")
            returnDetails.Title = myReader.Item("Title")
            returnDetails.Moderated = CYesNoToBool(myReader.Item("Moderated"))
            returnDetails.Importance = myReader.Item("Importance")
            If myReader.Item("Description") Is DBNull.Value Then
                returnDetails.Description = String.Empty
            Else
                returnDetails.Description = myReader.Item("Description")
            End If
            If myReader.Item("ImageUrl") Is DBNull.Value Then
                returnDetails.ImageURL = String.Empty
            Else
                returnDetails.ImageURL = myReader.Item("ImageUrl")
            End If
        Next
        Return returnDetails
    End Function

    Public Overrides Function GetForums() As System.Collections.Generic.List(Of DataTypes.ForumDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT ForumID, AddedDate, AddedBy, Title, Moderated, Importance, Description, ImageUrl " & _
        "FROM " & _TablePrefix & "_Forums " & _
        "ORDER BY Importance DESC, Title ASC"
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        Return GetForumCollectionFromReader(result)
    End Function

    Public Overrides Function GetPostBody(ByVal postID As String) As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT Body FROM " & _TablePrefix & "_Posts WHERE PostID = ?"
        DAP.AddParameter("@PostId", postID, DbType.String)
        Dim returnString As String = String.Empty
        returnString = DAP.ExecuteScalar(sSQL)
        Return returnString
    End Function

    Public Overrides Function GetPostByID(ByVal postID As String) As DataTypes.PostDetails
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT " & _TablePrefix & "_Posts.PostID, " & _TablePrefix & "_Posts.AddedDate, " & _TablePrefix & _
        "_Posts.AddedBy, " & _TablePrefix & "_Posts.AddedByIP, " & _TablePrefix & "_Posts.ForumID, " & _TablePrefix & _
        "_Posts.ParentPostID, " & _TablePrefix & "_Posts.Title, " & _TablePrefix & "_Posts.Body, " & _TablePrefix & _
        "_Posts.Approved, " & _TablePrefix & "_Posts.Closed, " & _TablePrefix & "_Posts.ViewCount, " & _TablePrefix & _
        "_Posts.ReplyCount, " & _TablePrefix & "_Posts.LastPostDate, " & _TablePrefix & "_Posts.LastPostBy," & _
        _TablePrefix & "_Forums.Title AS ForumTitle " & _
        "FROM " & _TablePrefix & "_Posts INNER JOIN " & _
        _TablePrefix & "_Forums ON " & _TablePrefix & "_Posts.ForumID = " & _TablePrefix & "_Forums.ForumID " & _
        "WHERE PostID = ?"
        DAP.AddParameter("@PostId", postID, DbType.String)
        Dim returnPost As New DataTypes.PostDetails
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        For Each myReader As Common.DbDataRecord In result
            returnPost.ID = myReader.Item("PostID")
            returnPost.AddedDate = myReader.Item("AddedDate")
            returnPost.AddedBy = myReader.Item("AddedBy")
            returnPost.AddedByIP = myReader.Item("AddedByIP")
            returnPost.ForumID = myReader.Item("ForumId")
            returnPost.ParentPostID = myReader.Item("ParentPostID")
            returnPost.Title = myReader.Item("Title")
            returnPost.Body = myReader.Item("Body")
            returnPost.Approved = CYesNoToBool(myReader.Item("Approved"))
            returnPost.Closed = CYesNoToBool(myReader.Item("Closed"))
            returnPost.ViewCount = myReader.Item("ViewCount")
            returnPost.ReplyCount = myReader.Item("ReplyCount")
            returnPost.LastPostDate = myReader.Item("LastPostDate")
            returnPost.LastPostBy = myReader.Item("LastPostBy")
            returnPost.ForumTitle = myReader.Item("ForumTitle")
        Next
        Return returnPost
    End Function

    Public Overrides Function GetPostCountByThread(ByVal threadPostID As String) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT COUNT(*) FROM " & _TablePrefix & "_Posts " & _
        "WHERE (PostID = ? OR ParentPostID = ?) AND Approved = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@PostId", threadPostID, DbType.String)
        DAP.AddParameter("@ParentPostId", threadPostID, DbType.String)
        DAP.AddParameter("@Approved", cTrueValue, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Return CInt(DAP.ExecuteScalar(sSQL))
    End Function

    Public Overrides Function GetPostCountByForum(ByVal forumID As String) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT COUNT(*) FROM " & _TablePrefix & "_Posts " & _
        "WHERE ForumID = ? AND Approved = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@ForumID", forumID, DbType.String)
        DAP.AddParameter("@Approved", cTrueValue, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Return CInt(DAP.ExecuteScalar(sSQL))
    End Function

    Public Overrides Function GetThreadByID(ByVal threadPostID As String) As System.Collections.Generic.List(Of DataTypes.PostDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT " & _TablePrefix & "_Posts.PostID, " & _TablePrefix & "_Posts.AddedDate, " & _TablePrefix & "_Posts.AddedBy, " & _
        _TablePrefix & "_Posts.AddedByIP, " & _TablePrefix & "_Posts.ForumID, " & _TablePrefix & "_Posts.ParentPostID, " & _TablePrefix & "_Posts.Title, " & _
        _TablePrefix & "_Posts.Body, " & _TablePrefix & "_Posts.Approved, " & _TablePrefix & "_Posts.Closed, " & _TablePrefix & "_Posts.ViewCount, " & _
        _TablePrefix & "_Posts.ReplyCount, " & _TablePrefix & "_Posts.LastPostDate, " & _TablePrefix & "_Posts.LastPostBy, " & _
        _TablePrefix & "_Forums.Title AS ForumTitle " & _
        "FROM " & _TablePrefix & "_Posts INNER JOIN " & _
        _TablePrefix & "_Forums ON " & _TablePrefix & "_Posts.ForumID = " & _TablePrefix & "_Forums.ForumID " & _
        "WHERE PostID = ? OR ParentPostID = ? AND " & _TablePrefix & "_Posts.LogicalDeleteInd = ? " & _
        "ORDER BY " & _TablePrefix & "_Posts.AddedDate ASC"
        DAP.AddParameter("@PostId", threadPostID, DbType.String)
        DAP.AddParameter("@ParentPostID", threadPostID, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Dim result As New Generic.List(Of Common.DbDataRecord)
        Dim returnCollection As New Generic.List(Of DataTypes.PostDetails)
        result = DAP.ExecuteReader(sSQL)
        For Each myReader As Common.DbDataRecord In result
            Dim newPostDetails As New DataTypes.PostDetails
            newPostDetails.ID = myReader.Item("PostID")
            newPostDetails.AddedDate = myReader.Item("AddedDate")
            newPostDetails.AddedBy = myReader.Item("AddedBy")
            newPostDetails.AddedByIP = myReader.Item("AddedByIP")
            newPostDetails.ForumID = myReader.Item("ForumID")
            newPostDetails.ParentPostID = myReader.Item("ParentPostID")
            newPostDetails.Title = myReader.Item("Title")
            newPostDetails.Body = myReader.Item("Body")
            newPostDetails.Approved = CYesNoToBool(myReader.Item("Approved"))
            newPostDetails.Closed = CYesNoToBool(myReader.Item("Closed"))
            newPostDetails.ViewCount = myReader.Item("ViewCount")
            newPostDetails.ForumTitle = myReader.Item("ForumTitle")
            returnCollection.Add(newPostDetails)
        Next
        Return returnCollection
    End Function

    Public Overloads Overrides Function GetThreadCount() As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT COUNT(*) FROM " & _TablePrefix & "_Posts" & _
        "WHERE ParentPostID = ? AND Approved = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@ParentPostId", String.Empty, DbType.String)
        DAP.AddParameter("@Approved", cTrueValue, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Return DAP.ExecuteScalar(sSQL)
    End Function

    Public Overloads Overrides Function GetThreadCount(ByVal forumID As String) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT COUNT(*) FROM " & _TablePrefix & "_Posts " & _
        "WHERE ForumID = ? AND ParentPostID = ? AND Approved = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@ForumId", forumID, DbType.String)
        DAP.AddParameter("@ParentPostId", String.Empty, DbType.String)
        DAP.AddParameter("@Approved", cTrueValue, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Return DAP.ExecuteScalar(sSQL)
    End Function

    Public Overloads Overrides Function GetThreads(ByVal forumID As String, ByVal sortExpression As String, ByVal pageIndex As Integer, ByVal pageSize As Integer) As System.Collections.Generic.List(Of DataTypes.PostDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        sortExpression = EnsureValidSortExpression(sortExpression)
        Dim lowerBound As Integer = pageIndex * pageSize + 1
        Dim upperBound As Integer = (pageIndex + 1) * pageSize
        'TODO: Lookup what the ROWNUM() Function is doing and figure out this SQL
        Dim sSQL As String = String.Format("SELECT * FROM(" & _
            "SELECT " & _TablePrefix & "_Posts.PostID, " & _TablePrefix & "_Posts.AddedDate, " & _
            _TablePrefix & "_Posts.AddedBy, " & _TablePrefix & "_Posts.AddedByIP, " & _
            _TablePrefix & "_Posts.ForumID, " & _TablePrefix & "_Posts.ParentPostID, " & _
            _TablePrefix & "_Posts.Title, " & _TablePrefix & "_Posts.Approved, " & _
            _TablePrefix & "_Posts.Closed, " & _TablePrefix & "_Posts.ViewCount, " & _
            _TablePrefix & "_Posts.ReplyCount, " & _TablePrefix & "_Posts.LastPostDate, " & _
            _TablePrefix & "_Posts.LastPostBy, " & _TablePrefix & "_Forums.Title AS ForumTitle, " & _
            "ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum " & _
            "FROM " & _TablePrefix & "_Posts INNER JOIN " & _TablePrefix & "_Forums ON " & _
            _TablePrefix & "_Posts.ForumID = " & _TablePrefix & "_Forums.ForumID " & _
            "WHERE (ParentPostID = ? And Approved = ? AND LogicalDeleteInd = ?)) ForumThreads " & _
            "WHERE ForumThreads.RowNum BETWEEN {1} AND {2} " & _
            "ORDER BY RowNum ASC", _
            _TablePrefix & sortExpression, lowerBound, upperBound)
        DAP.AddParameter("@ParentPostId", String.Empty, DbType.String)
        DAP.AddParameter("@Approved", cTrueValue, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Return GetPostCollectionFromReader(DAP.ExecuteReader(sSQL), False)
    End Function

    Public Overloads Overrides Function GetThreads(ByVal forumID As String) As System.Collections.Generic.List(Of DataTypes.PostDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT " & _TablePrefix & "_Posts.PostID, " & _TablePrefix & "_Posts.AddedDate, " & _
            _TablePrefix & "_Posts.AddedBy, " & _TablePrefix & "_Posts.AddedByIP, " & _
            _TablePrefix & "_Posts.ForumID, " & _TablePrefix & "_Posts.ParentPostID, " & _
            _TablePrefix & "_Posts.Title, " & _TablePrefix & "_Posts.Approved, " & _
            _TablePrefix & "_Posts.Closed, " & _TablePrefix & "_Posts.ViewCount, " & _
            _TablePrefix & "_Posts.ReplyCount, " & _TablePrefix & "_Posts.LastPostDate, " & _
            _TablePrefix & "_Posts.LastPostBy, " & _TablePrefix & "_Forums.Title AS ForumTitle " & _
            "FROM " & _TablePrefix & "_Posts INNER JOIN " & _TablePrefix & "_Forums ON " & _
            _TablePrefix & "_Posts.ForumID = " & _TablePrefix & "_Forums.ForumID " & _
            "WHERE Approved = ? AND " & _TablePrefix & "_Posts.LogicalDeleteInd = ? AND " & _TablePrefix & "_Posts.ForumID = ? " & _
            "AND " & _TablePrefix & "_Posts.ParentPostId = ''"
        DAP.AddParameter("@Approved", cTrueValue, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        DAP.AddParameter("@ForumID", forumID, DbType.String)
        Return GetPostCollectionFromReader(DAP.ExecuteReader(sSQL), False)
    End Function

    Public Overloads Overrides Function GetThreads(ByVal sortExpression As String, ByVal pageIndex As Integer, ByVal pageSize As Integer) As System.Collections.Generic.List(Of DataTypes.PostDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        sortExpression = EnsureValidSortExpression(sortExpression)
        Dim lowerBound As Integer = pageIndex * pageSize + 1
        Dim upperBound As Integer = (pageIndex + 1) * pageSize
        Dim sSQL As String = String.Format("SELECT * FROM(" & _
            "SELECT " & _TablePrefix & "_Posts.PostID, " & _TablePrefix & "_Posts.AddedDate," & _
            _TablePrefix & "_Posts.AddedBy, " & _TablePrefix & "_Posts.AddedByIP, " & _
            _TablePrefix & "_Posts.ForumID, " & _TablePrefix & "_Posts.ParentPostID, " & _
            _TablePrefix & "_Posts.Title, " & _TablePrefix & "_Posts.Approved, " & _
            _TablePrefix & "_Posts.Closed, " & _TablePrefix & "_Posts.ViewCount, " & _
            _TablePrefix & "_Posts.ReplyCount, " & _TablePrefix & "_Posts.LastPostDate, " & _
            _TablePrefix & "_Posts.LastPostBy, " & _TablePrefix & "_Forums.Title AS ForumTitle, " & _
                "ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum " & _
                "FROM " & _TablePrefix & "_Posts INNER JOIN tbh_Forums ON " & _
                _TablePrefix & "_Posts.ForumID = " & _TablePrefix & "_Forums.ForumID " & _
                "WHERE (ParentPostID = ? And Approved = ? AND LogicalDeleteInd = ?)) ForumThreads " & _
            "WHERE ForumThreads.RowNum BETWEEN {1} AND {2} " & _
            "ORDER BY RowNum ASC", _
            _TablePrefix & sortExpression, lowerBound, upperBound)
        DAP.AddParameter("@ParentPostId", String.Empty, DbType.String)
        DAP.AddParameter("@Approved", cTrueValue, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Return GetPostCollectionFromReader(DAP.ExecuteReader(sSQL), False)
    End Function

    Public Overrides Function GetUnapprovedPosts() As System.Collections.Generic.List(Of DataTypes.PostDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT " & _TablePrefix & "_Posts.PostID, " & _TablePrefix & "_Posts.AddedDate, " & _
        _TablePrefix & "_Posts.AddedBy, " & _
        _TablePrefix & "_Posts.AddedByIP, " & _TablePrefix & "_Posts.ForumID, " & _TablePrefix & "_Posts.ParentPostID, " & _
        _TablePrefix & "_Posts.Title, " & _TablePrefix & "_Posts.Approved, " & _TablePrefix & "_Posts.Closed, " & _TablePrefix & "_Posts.ViewCount, " & _
        _TablePrefix & "_Posts.ReplyCount, " & _TablePrefix & "_Posts.LastPostDate, " & _TablePrefix & "_Posts.LastPostBy," & _
        _TablePrefix & "_Forums.Title AS ForumTitle, " & _
        "FROM " & _TablePrefix & "_Posts INNER JOIN" & _
        _TablePrefix & "_Forums ON " & _TablePrefix & "_Posts.ForumID = " & _TablePrefix & "_Forums.ForumID" & _
        "WHERE Approved = ?)" & _
        "ORDER BY AddedDate ASC"
        DAP.AddParameter("@Approved", cTrueValue, DbType.String)
        Return GetPostCollectionFromReader(DAP.ExecuteReader(sSQL), False)
    End Function

    Public Overrides Function IncrementPostViewCount(ByVal postID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim CurrentViewCount As Integer = 0
        Dim sSQL As String
        sSQL = "SELECT ViewCount FROM " & _TablePrefix & "_Posts WHERE PostId = ?"
        DAP.AddParameter("@PostId", postID, DbType.String)
        CurrentViewCount = DAP.ExecuteScalar(sSQL)

        sSQL = "UPDATE " & _TablePrefix & "_Posts " & _
        "SET ViewCount = ? " & _
        "WHERE PostID = ?"
        DAP.AddParameter("@ViewCount", (CurrentViewCount + 1), DbType.Int64)
        DAP.AddParameter("@PostId", postID, DbType.String)
        If DAP.ExecuteNonQuery(sSQL) >= 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Overrides Function InsertForum(ByVal title As String, ByVal moderated As Boolean, _
                ByVal importance As Integer, ByVal description As String, ByVal imageUrl As String, ByVal Username As String) As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim newForumId As String = System.Guid.NewGuid.ToString
        Dim sSQL As String = "INSERT INTO " & _TablePrefix & "_Forums " & _
        "(ForumId, AddedDate, AddedBy, Title, Moderated, Importance, Description, ImageUrl, LogicalDeleteInd) " & _
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
        DAP.AddParameter("@ForumID", newForumId, DbType.String)
        DAP.AddParameter("@AddedDate", Now(), DbType.DateTime)
        DAP.AddParameter("@AddedBy", Username, DbType.String)
        DAP.AddParameter("@Title", title, DbType.String)
        DAP.AddParameter("@Moderated", CBoolToYesNo(moderated), DbType.String)
        DAP.AddParameter("@Importance", importance, DbType.Int64)
        If String.IsNullOrEmpty(description) Then
            DAP.AddParameter("@Description", DBNull.Value, DbType.String)
        Else
            DAP.AddParameter("@Description", description, DbType.String)
        End If
        If String.IsNullOrEmpty(imageUrl) Then
            DAP.AddParameter("@ImageUrl", DBNull.Value, DbType.String)
        Else
            DAP.AddParameter("@ImageUrl", imageUrl, DbType.String)
        End If
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        If DAP.ExecuteNonQuery(sSQL) >= 1 Then
            Return newForumId
        Else
            Return String.Empty
        End If
    End Function

    Public Overrides Function InsertPost(ByVal post As DataTypes.PostDetails) As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim newPostID As String = System.Guid.NewGuid.ToString
        Dim returnVal As String = String.Empty
        Dim sSQL As String = "INSERT INTO " & _TablePrefix & "_Posts " & _
        "(PostID, AddedDate, AddedBy, AddedByIP, ForumID, " & _
        "ParentPostID, Title, Body, Approved, Closed, LastPostDate, LastPostBy, ViewCount, ReplyCOunt, LogicalDeleteInd) " & _
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        DAP.AddParameter("@PostId", newPostID, DbType.String)
        DAP.AddParameter("@AddedDate", Now(), DbType.DateTime)
        DAP.AddParameter("@AddedBy", post.AddedBy, DbType.String)
        DAP.AddParameter("@AddedByIP", post.AddedByIP, DbType.String)
        DAP.AddParameter("@ForumID", post.ForumID, DbType.String)
        DAP.AddParameter("@ParentPostId", post.ParentPostID, DbType.String)
        DAP.AddParameter("@Title", post.Title, DbType.String)
        DAP.AddParameter("@Body", post.Body, DbType.String)
        DAP.AddParameter("@Approved", CBoolToYesNo(post.Approved), DbType.String)
        DAP.AddParameter("@Closed", CBoolToYesNo(post.Closed), DbType.String)
        DAP.AddParameter("@LastPostDate", Now(), DbType.DateTime)
        DAP.AddParameter("@LastPostBy", post.LastPostBy, DbType.String)
        DAP.AddParameter("@ViewCount", post.ViewCount, DbType.Int64)
        DAP.AddParameter("@ReplyCount", post.ViewCount, DbType.Int64)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)

        If DAP.ExecuteNonQuery(sSQL) >= 1 Then
            returnVal = newPostID
        Else
            returnVal = String.Empty
        End If
        If post.Approved And (String.IsNullOrEmpty(post.ParentPostID) = False) Then
            sSQL = "UPDATE " & _TablePrefix & "_Posts SET ReplyCount = ReplyCount + 1, " & _
            "LastPostDate = ?, LastPostBy = ? " & _
            "WHERE PostID = ?"
            DAP.AddParameter("@LastPostDate", post.AddedDate, DbType.DateTime)
            DAP.AddParameter("@LastPostBy", post.AddedBy, DbType.String)
            DAP.AddParameter("@PostId", post.ParentPostID, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        End If
        Return returnVal
    End Function

    Public Overrides Function MoveThread(ByVal threadPostID As String, ByVal forumID As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Posts" & _
        "SET ForumID = ? " & _
        "WHERE PostID = ? OR ParentPostID = ?"
        DAP.AddParameter("@ForumID", forumID, DbType.String)
        DAP.AddParameter("@PostID", threadPostID, DbType.String)
        DAP.AddParameter("@ParentPostID", threadPostID, DbType.String)
        If DAP.ExecuteNonQuery(sSQL) >= 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Overrides Function UpdateForum(ByVal forum As DataTypes.ForumDetails) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Forums" & _
        "SET Title = ?, Moderated = ?, Importance = ?, Description = ?, ImageUrl = ? " & _
        "WHERE ForumID = ?"
        DAP.AddParameter("@ForumID", forum.ID, DbType.String)
        DAP.AddParameter("@Title", forum.Title, DbType.String)
        DAP.AddParameter("@Moderated", CBoolToYesNo(forum.Moderated), DbType.String)
        DAP.AddParameter("@Importance", forum.Importance, DbType.Int64)
        DAP.AddParameter("@ImageUrl", forum.ImageURL, DbType.String)
        DAP.AddParameter("@Description", forum.Description, DbType.String)
        Dim ret As Integer = DAP.ExecuteNonQuery(sSQL)
    End Function

    Public Overrides Function UpdatePost(ByVal post As DataTypes.PostDetails) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Posts " & _
        "SET Title = ?, Body = ? WHERE PostID = ?"
        DAP.AddParameter("@Title", post.Title, DbType.String)
        DAP.AddParameter("@Body", post.Body, DbType.String)
        DAP.AddParameter("@PostID", post.ID, DbType.String)
        If DAP.ExecuteNonQuery(sSQL) >= 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Overrides Function GetLatestPost(ByVal forumID As String) As DataTypes.PostDetails
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT * FROM " & _
        _TablePrefix & "_Posts WHERE ForumID = ? AND " & _
        "AddedDate = (SELECT MAX(AddedDate) FROM " & _TablePrefix & "_Posts WHERE " & _
        "ForumID = ?)"
        DAP.AddParameter("@ForumID", forumID, DbType.String)
        DAP.AddParameter("@ForumID1", forumID, DbType.String)
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        Dim post As New DataTypes.PostDetails
        For Each myReader As Common.DbDataRecord In result
            post.ID = myReader("PostID").ToString
            post.AddedDate = CDate(myReader("AddedDate"))
            post.AddedBy = myReader("AddedBy").ToString()
            post.AddedByIP = myReader("AddedByIP").ToString()
            post.ForumID = myReader("ForumID").ToString
            post.ParentPostID = myReader("ParentPostID").ToString
            post.Title = myReader("Title").ToString()
            post.Body = myReader("Body").ToString()
            post.Approved = CYesNoToBool(myReader("Approved"))
            post.Closed = CYesNoToBool(myReader("Closed"))
            post.ViewCount = CInt(myReader("ViewCount"))
            post.ReplyCount = CInt(myReader("ReplyCount"))
            post.LastPostDate = CDate(myReader("LastPostDate"))
            post.LastPostBy = myReader("LastPostBy").ToString()
        Next
        Return post
    End Function

    Private Function CBoolToYesNo(ByVal InputValue As Boolean) As String
        If InputValue = True Then
            Return "Y"
        Else
            Return "N"
        End If
    End Function

    Private Function CYesNoToBool(ByVal InputString As String) As Boolean
        If InputString.ToLower = "y" Then
            Return True
        Else
            Return False
        End If
    End Function
End Class
